﻿
Module ProjectMemberDao

    Public Function searchDataByFilter(ByRef projectMember As ProjectMember) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim resource As New Resource
        Dim role As New Role
        Dim projectMemberRole As New ProjectMemberRole

        Try
            Dim query As String = ""

            query = " SELECT PM.id                   PM_ID, "
            query &= "        PM.available_start_date PM_available_start_date,"
            query &= "        PM.available_end_date   PM_available_end_date,"
            query &= "               PM.resource_id          PM_RESOURCE_ID,"
            query &= "               PM.project_id           PM_PROJECT_ID,"
            query &= "               RE.id                   RE_ID,"
            query &= "               RE.firstname            RE_FIRSTNAME,"
            query &= "               (RE.surname || ', ' ||  RE.firstname) as name,"
            query &= "               RE.surname              RE_SURNAME,"
            query &= "               RE.document_type        RE_DOCUMENT_TYPE,"
            query &= "               RE.document_number      RE_DOCUMENT_NUMBER,"
            query &= "               RE.email                RE_EMAIL,"
            query &= "               RE.create_date          RE_CREATE_DATE,"
            query &= "               RE.end_date             RE_END_DATE,"
            query &= "               RE.hour_cost            RE_HOUR_COST,"
            query &= "               RE.state_id             RE_STATE_ID"
            query &= "          FROM " & projectMember.getTableName & " pm, " & resource.getTableName & " re "
            query &= "         WHERE (pm.project_id = :project_id OR 0 = :project_id)"
            query &= "           AND (pm.resource_id = :resource_id OR 0 = :resource_id)"
            query &= "           AND re.id = pm.resource_id "

            command.CommandType = CommandType.Text
            command.CommandText = query
            command.Connection = connection
            command.Parameters.AddWithValue("project_id", projectMember.projectProperty.idProperty)
            command.Parameters.AddWithValue("resource_id", If(projectMember.resourceProperty Is Nothing, Nothing, projectMember.resourceProperty.idProperty))

            table.Load(command.ExecuteReader)
        Catch ex As Exception
            DataAccessConnection.closeConnection()
        End Try

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchData(ByRef projectMember As ProjectMember) As ArrayList
        Dim table As New Data.DataTable
        Dim list As New ArrayList
        table = searchDataByFilter(projectMember)
        list = convertTableToList(table)
        Return list
    End Function

    Private Function convertTableToList(ByRef table As Data.DataTable) As ArrayList
        Dim listProjectMembers As New ArrayList

        For Each row In table.Rows
            Dim projectMember As New ProjectMember
            projectMember.idProperty = row("PM_ID")
            projectMember.availableFromDateProperty = row("PM_available_start_date")
            projectMember.availableToDateProperty = row("PM_available_end_date")

            Dim resource As New Resource
            resource.idProperty = row("RE_ID")
            resource.firstnameProperty = row("RE_FIRSTNAME")
            resource.surnameProperty = row("RE_SURNAME")
            projectMember.resourceProperty = resource

            Dim listProjectMemberRole As New ArrayList
            Dim projectMemberRole As New ProjectMemberRole(projectMember)
            listProjectMemberRole = ProjectMemberRoleDao.searchData(projectMemberRole)
            projectMember.projectMemberRoleProperty = listProjectMemberRole

            listProjectMembers.Add(projectMember)
        Next


        Return listProjectMembers
    End Function
End Module
